package com.demo.utils;

import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class JDBCUtils {
    private static String url;
    private static String username;
    private static String password;

    static {
        // 读取 src 目录下的文件
        InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("JDBC.properties");
        Properties prop = new Properties();
        try {
            prop.load(is);
            url = prop.getProperty("url");
            username = prop.getProperty("username");
            password = prop.getProperty("password");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    private static Connection conn = null;
    /**
     * 获取数据库的链接
     */
    public static Connection getConn(){
        try {
            if (conn == null) {
                conn = DriverManager.getConnection(url, username, password);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void close(){
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     *
     * @param sql sql语句
     * @param params 替换sql语句中占位符的值
     * @return 执行的影响的行数
     */
    private static int handleDBUpdate(String sql, String... params) throws SQLException {
        Connection conn = JDBCUtils.getConn();
        PreparedStatement statement = conn.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            statement.setString(i + 1, params[i]);
        }
        return statement.executeUpdate();
    }

    /**
     *
     * @param sql sql语句
     * @param clazz 查询表明所对应的bean类
     * @param params sql语句中的占位符 替换者
     * @return List<T> 查询到的结果集 javaBean形式的
     */
    public static <T> List<T> handleDBQuery(String sql, Class<T> clazz, String... params) throws Exception {
        Connection conn = JDBCUtils.getConn();
        PreparedStatement statement = conn.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            statement.setString(i + 1, params[i]);
        }
        ArrayList<T> userList = new ArrayList<>();
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()){
//          动态获取resultSet中的数据，兼容多列或一列的情况
            T instance = clazz.getConstructor().newInstance();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 0; i < columnCount; i++) {
                String columnName = metaData.getColumnLabel(i + 1);
                // 根据列名去除一行中对应的列的数据
                String itemData = resultSet.getString(columnName);
                // 将取出数据存放到 bean 中    内省
                BeanInfo beanInfo = Introspector.getBeanInfo(clazz, Object.class);
                PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
                PropertyDescriptor itemEnd = null;
                for (PropertyDescriptor item : propertyDescriptors) {
                    if(item.getName().equals(columnName)){
                        itemEnd = item;
                        break;
                    }
                }
                Method method = itemEnd.getWriteMethod();
                method.invoke(instance, itemData);
            }
            userList.add(instance);
        }
        return userList;
    }
}
